﻿using System;
using System.Collections.Generic;
using System.Text;
using CeA.Reports.Atendimento.DataAccess.DTO;
using System.Data.OracleClient;
using System.Data;
using CeA.Reports.Atendimento.DataAccess.Helpers;
using CeA.Reports.Atendimento.DataAccess.Criteria;
using IBatisNet.DataMapper;

namespace CeA.Reports.Atendimento.DataAccess.DAO.Oracle {
    public class OracleRelatorioServicoImpl : RelatorioServico {
        OracleConnection m_connection = null;
        StringBuilder sql = new StringBuilder();

        /// <summary>
        /// Initializes a new instance of the <see cref="OracleRelatorioServicoImpl"/> class.
        /// </summary>
        public OracleRelatorioServicoImpl()                                                 {
            m_connection = OracleDAOFactory.CreateConnection();
        }


        /// <summary>
        /// Lista o tempo médio de atendimento sem critério de busca.
        /// </summary>
        /// <returns></returns>
        public RelatorioServicoDTO FindRelatorioServico()                                   {
            //TODO: LUCIANO
            return null;
        }


        /// <summary>
        /// Lista o tempo médio de atendimento.
        /// </summary>
        public RelatorioServicoDTO FindRelatorioServicoBy(RelatorioServicoFilter filter)    {
            OracleCommand cmd = m_connection.CreateCommand();
            StringBuilder sql = new StringBuilder();
            String sqlCase = MontaSQLCases(filter);
            String sqlWhere = MontaSQLWhere(filter);


            sql.AppendFormat("SELECT LJ.LOJA_ID, RG.REGIAO_NOME,  LJ.LOJA_SIGLA,                 \n");
            sql.AppendFormat(sqlCase + " \n");
            sql.AppendFormat(" COUNT(*) AS XXXX_TOTAL                           \n");
            sql.AppendFormat("FROM VMATTER_MATERTYP_VISIT VS                    \n");
            sql.AppendFormat("  INNER JOIN LOJA LJ ON LJ.QMATIC_BRANCHNO = VS.BRANCHNO \n");
            sql.AppendFormat("  INNER JOIN RFS RF ON RF.RFS_ID = LJ.RFS_ID             \n");
            sql.AppendFormat("  INNER JOIN REGIAO RG ON RG.REGIAO_ID = LJ.REGIAO_ID    \n");

            sql.AppendFormat(sqlWhere + " \n");
            sql.AppendFormat("GROUP BY  RG.REGIAO_NOME, LJ.LOJA_ID, LJ.LOJA_SIGLA                \n");
            sql.AppendFormat("ORDER BY  RG.REGIAO_NOME, LJ.LOJA_ID, LJ.LOJA_SIGLA                \n");

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql.ToString();

            OracleDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            RelatorioServicoDTO dto = new RelatorioServicoDTO();

            DataTable table = new DataTable();
            table.Load(odr);

            dto.TableRelatorio = table;

            Console.WriteLine(sql.ToString());

            return dto;
        }

        /// <summary>
        /// Lista o total de seviço RFS
        /// </summary>
        public RelatorioServicoDTO FindRelatorioServicoByRFS(RelatorioServicoFilter filter)
        {
            OracleCommand cmd = m_connection.CreateCommand();
            StringBuilder sql = new StringBuilder();
            String sqlCase = MontaSQLCases(filter);
            String sqlWhere = " WHERE 1=1 \n";
            sqlWhere += "\n AND TO_CHAR(VS.regdate, 'yyyymm') = " + filter.DataIncidencia.ToString("yyyyMM");


            sql.AppendFormat("SELECT RF.RFS_NOME,                                      \n");
            sql.AppendFormat(sqlCase +                                               " \n");
            sql.AppendFormat(" COUNT(*) AS XXXX_TOTAL                                  \n");
            sql.AppendFormat("FROM VMATTER_MATERTYP_VISIT VS                           \n");
            sql.AppendFormat("  INNER JOIN LOJA LJ ON LJ.QMATIC_BRANCHNO = VS.BRANCHNO \n");
            sql.AppendFormat("  INNER JOIN RFS RF ON RF.RFS_ID = LJ.RFS_ID             \n");
            sql.AppendFormat("  INNER JOIN REGIAO RG ON RG.REGIAO_ID = LJ.REGIAO_ID    \n");

            sql.AppendFormat(sqlWhere +                                              " \n");
            sql.AppendFormat("GROUP BY RF.RFS_NOME                          \n");
            sql.AppendFormat("ORDER BY RF.RFS_NOME                          \n");

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql.ToString();

            OracleDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            RelatorioServicoDTO dto = new RelatorioServicoDTO();

            DataTable table = new DataTable();
            table.Load(odr);

            dto.TableRelatorio = table;

            Console.WriteLine(sql.ToString());

            return dto;
        }
        
        /// <summary>
        /// Lista o total de seviço Brasil
        /// </summary>
        public RelatorioServicoDTO FindRelatorioServicoByBrasil(RelatorioServicoFilter filter)
        {
            OracleCommand cmd = m_connection.CreateCommand();
            StringBuilder sql = new StringBuilder();
            String sqlCase = MontaSQLCases(filter);
            String sqlWhere = " WHERE 1=1 \n";
                   sqlWhere += "\n AND TO_CHAR(VS.regdate, 'yyyymm') = " + filter.DataIncidencia.ToString("yyyyMM");


            sql.AppendFormat("SELECT 'BRASIL',                 \n");
            sql.AppendFormat(sqlCase + " \n");
            sql.AppendFormat(" COUNT(*) AS XXXX_TOTAL                           \n");
            sql.AppendFormat("FROM VMATTER_MATERTYP_VISIT VS                    \n");
            sql.AppendFormat("  INNER JOIN LOJA LJ ON LJ.QMATIC_BRANCHNO = VS.BRANCHNO \n");
            sql.AppendFormat("  INNER JOIN RFS RF ON RF.RFS_ID = LJ.RFS_ID             \n");
            sql.AppendFormat("  INNER JOIN REGIAO RG ON RG.REGIAO_ID = LJ.REGIAO_ID    \n");

            sql.AppendFormat(sqlWhere + " \n");
            
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql.ToString();

            OracleDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            RelatorioServicoDTO dto = new RelatorioServicoDTO();

            DataTable table = new DataTable();
            table.Load(odr);

            dto.TableRelatorio = table;

            Console.WriteLine(sql.ToString());

            return dto;
        }



        /// <summary>
        /// Monta a lista de colunas conforme lista de serviço
        /// </summary>
        private string MontaSQLCases(RelatorioServicoFilter filter)                         {
            string sql_cases = "";

            if (filter.ServicoList.Count > 0) {
                foreach (int serv in filter.ServicoList) {
                    //sql_cases += " SUM(CASE WHEN VS.CODE = " + serv.ToString() + " THEN 1 ELSE 0 END) AS \"" + serv.ToString() + "_QT\", \n";
                    //sql_cases += " CAST(AVG(CASE WHEN VS.CODE = " + serv.ToString() + " THEN VS.TempoAtendimento ELSE 0 END)as number(6,2)) AS \"" + serv.ToString() + "_TMA\", \n";
                    //sql_cases += " CAST(((SUM(CASE WHEN VS.CODE = " + serv.ToString() + " THEN 1 ELSE 0 END)/ COUNT(*)) * 100)as number(6,2)) AS \"" + serv.ToString() + "_%\" , \n";

                    sql_cases += " SUM(CASE WHEN VS.CODE = " + serv.ToString() + " THEN 1 ELSE 0 END) AS \"" + serv.ToString() + "_QT\", \n";
                    sql_cases += " CAST((SUM(CASE WHEN VS.CODE = " + serv.ToString() + " THEN VS.TempoAtendimento ELSE 0 END)/ (CASE WHEN (SUM(CASE WHEN VS.CODE = " + serv.ToString() + " THEN 1 ELSE 0 END) = 0) THEN 1 ELSE (SUM(CASE WHEN VS.CODE = " + serv.ToString() + " THEN 1 ELSE 0 END)) END))as number(6,2)) AS \"" + serv.ToString() + "_TMA\", \n";
                    sql_cases += " CAST(((SUM(CASE WHEN VS.CODE = " + serv.ToString() + " THEN 1 ELSE 0 END)/ COUNT(*)) * 100)as number(6,2)) AS \"" + serv.ToString() + "_%\" , \n";
                }
            }

            return sql_cases;

        }


        /// <summary>
        /// monta o filtro do where
        /// </summary>
        private string MontaSQLWhere(RelatorioServicoFilter filter)                         {
            string sql_where = " WHERE 1=1 \n";           
            sql_where += "\n AND TO_CHAR(VS.regdate, 'yyyymm') = " + filter.DataIncidencia.ToString("yyyyMM");

            //filtro das regioes
            if (filter.RfsList.Count >= 1 && filter.RfsList[0] != 0){
                sql_where += " AND RF.RFS_ID IN (";
                foreach (int rfs in filter.RfsList)
                {
                    sql_where += rfs.ToString() + " , ";
                }
                sql_where = sql_where.Substring(0, sql_where.Length - 2);
                sql_where += ") ";
            }

            //filtro das regioes
            if (filter.RegiaoList.Count >= 1 && filter.RegiaoList[0] != 0){
                sql_where += " AND RG.REGIAO_ID IN (";
                foreach (int regiao in filter.RegiaoList)
                {
                    sql_where += regiao.ToString() + " , ";
                }
                sql_where = sql_where.Substring(0, sql_where.Length - 2);
                sql_where += ") ";
            }
           
            //filtro das lojas
            if (filter.LojaList.Count >= 1 && filter.LojaList[0] != 0){
                sql_where += " AND LJ.LOJA_ID IN (";
                foreach (int loja in filter.LojaList)
                {
                    sql_where += loja.ToString() + " , ";
                }
                sql_where = sql_where.Substring(0, sql_where.Length - 2);
                sql_where += ") ";
            }
             
            return sql_where;
        }
    }
}

